package database_package;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Observable;
import java.util.Observer;

import frontend_package.Listeners;

import backend_package.Analyst;
import backend_package.Login;
import backend_package.Packet;

/**
 * This class deals with the database in the background. It manages the all the
 * data that application works on. Uses Observer design pattern.
 * 
 * @author Syrym
 * */
public class SqliteDatabase extends Observable implements Observer {
	/**
	 * Connection to the database instance.
	 * 
	 * @author Syrym
	 * */
	Connection conn = null;
	/**
	 * Constant for user detection.
	 * 
	 * @author Syrym
	 * */
	final static int ISUSER = 0;
	/**
	 * Constant for admin detection.
	 * 
	 * @author Syrym
	 * */
	final static int ISADMIN = 1;
	/**
	 * The list of assigned RFPS.
	 * 
	 * @author Syrym
	 * */
	public ArrayList<String> assignedRFPlist;
	/**
	 * The list of RFP and DRAFTS.
	 * 
	 * @author Syrym
	 * */
	public Hashtable<String, String> workingRFPsNdrafts;
	/**
	 * The current user name.
	 * 
	 * @author Syrym
	 * */
	private String username;

	/**
	 * The constructor of the Class.
	 * 
	 * @author Syrym
	 * */
	public SqliteDatabase() {
		assignedRFPlist = new ArrayList<String>();
		workingRFPsNdrafts = new Hashtable<String, String>();
		conn = SqliteConnection.dbConnect();
	}

	/**
	 * The method to add the draft file path to its current record
	 * 
	 * @author Syrym
	 */
	public void updateUserData(String name, String RFPpath, String newDraftPath) {
		try {
			Statement stat = conn.createStatement();
			stat.executeUpdate("UPDATE user_rfps_drafts SET drafts='"
					+ newDraftPath + "' WHERE username='" + name
					+ "' AND rfps='" + RFPpath + "';");
			stat.close();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * The method to retrieve all the assigned RFPs for the current user.
	 * 
	 * @author Syrym
	 * @param username
	 *            the string username.
	 * */
	private void retrieveAssignedRFPs(String username) {
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat
					.executeQuery("SELECT rfps FROM user_rfps_drafts WHERE username ='"
							+ username.toLowerCase() + "' AND drafts IS NULL;");
			assignedRFPlist.clear();
			while (rs.next()) {
				assignedRFPlist.add(rs.getString("rfps"));
			}
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * The method to retrieve all the currently working RFPs and drafts for the
	 * current user.
	 * 
	 * @author Syrym
	 * @param username
	 *            the user name.
	 * */
	private void retrieveWorkingRFPsNdrafts(String username) {
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat
					.executeQuery("SELECT rfps,drafts FROM user_rfps_drafts WHERE username ='"
							+ username.toLowerCase()
							+ "' AND drafts IS NOT NULL;");
			workingRFPsNdrafts.clear();
			while (rs.next()) {
				workingRFPsNdrafts.put(rs.getString("rfps"),
						rs.getString("drafts"));
			}
			stat.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * The wrapper method to get user data, delegates the details to other
	 * methods.
	 * 
	 * @author Syrym
	 * @param name
	 *            is the user name.
	 * */
	private void getUserData(String name) {
		username = name;
		retrieveAssignedRFPs(username);
		retrieveWorkingRFPsNdrafts(username);
	}

	/**
	 * The method to send the user data to its Observers.
	 * 
	 * @author Syrym
	 * */
	private void sendUserData() {
		ArrayList<String> assignedrfplist = new ArrayList<String>(
				assignedRFPlist);
		Hashtable<String, String> workingrfpsndrafts = new Hashtable<String, String>(
				workingRFPsNdrafts);
		Object[] oarr = { assignedrfplist, workingrfpsndrafts };
		Packet packet = new Packet("db_userdata_a", oarr, username);
		setChanged();
		notifyObservers(packet); // notifies analyst 
		notifyHeaderPanel(username);
	}
	/**
	 * Notifies the HeaderPanel about username.
	 * @author Syrym
	 * @param name is the user name.
	 * */
	private void notifyHeaderPanel(String name){
		setChanged();
		notifyObservers(new Packet("db_userdata_hp", name));
	}
	/**
	 * The method to check if the user registered.
	 * 
	 * @author Syrym
	 * @param a_name
	 *            is the possible user name.
	 * @param a_pass
	 *            is the possible password name.
	 * @param result
	 *            is the array of booleans to see if registered and if its an
	 *            admin.
	 * */
	public Boolean[] isUserRegistered(String a_name, String a_pass) {
		Boolean[] result = new Boolean[2];
		result[0] = false;
		result[1] = false;
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat
					.executeQuery("SELECT * FROM user_credentials WHERE username = '"
							+ a_name.toLowerCase() + "';");
			if (rs.next()) {// returns true if there is such record
				String pass = rs.getString("password");
				String name = rs.getString("username");
				boolean isAdmin = rs.getBoolean("isAdmin");

				stat.close();

				if (a_name.equals(name) && a_pass.equals(pass) && !isAdmin) {
					result[0] = true;
					result[1] = false;
					getUserData(name);
				} else if (a_name.equals(name) && a_pass.equals(pass)) {
					result[0] = true;
					result[1] = true;
					notifyHeaderPanel("admin");
				} 
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	/**
	 * The observer design pattern method, to get the notifications (packets).
	 * 
	 * @author Syrym
	 * @param o
	 *            is the Observable class who is talking to this class.
	 * @param arg
	 *            is the Object (packet) that Observable class sent to this
	 *            class.
	 * */
	@Override
	public void update(Observable o, Object arg) {
		Packet packet = (Packet) arg;
		if (o instanceof Login && packet.getTag().equals("lp_isvaliduser")) {
			Boolean[] result = isUserRegistered((String) packet.get(0),
					(String) packet.get(1));
			setChanged();
			notifyObservers(new Packet("db_isvaliduser", result));
		} else if (o instanceof Analyst
				&& packet.getTag().equals("a_getuserdata")) {
			sendUserData();
		} else if (o instanceof Listeners
				&& packet.getTag().equals("lis_updatedata_db")) {
			String name = packet.getUsername();
			String rfp = (String) packet.get(0);
			String newdraft = (String) packet.get(1);
			updateUserData(name, rfp, newdraft);
		}
	}
}
